package com.chima.dao;

import java.util.List;
import org.hibernate.Session;
import org.hibernate.criterion.DetachedCriteria;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.chima.domain.AllRewardPercent;
import com.chima.domain.DepartJobfinace;
import com.chima.domain.Department;
import com.chima.domain.DepartmentCoffer;
import com.chima.domain.DepartmentFinace;
import com.chima.domain.Finace;
import com.chima.domain.FinacePercent;
import com.chima.domain.LeaderReward;
import com.chima.domain.MiddleReward;
import com.chima.domain.RewardStock;

public class DepartmentFinaceDao extends HibernateDaoSupport{
	
	//分页查询
	
	public List< DepartmentFinace> findByCriteria(DetachedCriteria criteria, int firstResult,int maxResults) {
		return this.getHibernateTemplate().findByCriteria(criteria,firstResult,maxResults);
	}
   //查找总页数
	public int findByTotalCount(String date) {
		List list= this.getHibernateTemplate().find("select count(*) from DepartmentFinace where date='"+date+"'");
		long temp = (Long) list.get(0);
		return (int) temp;
	}
	
	public void save(DepartmentFinace departmentFinace) {
		Session session=this.getSession();
		session.save(departmentFinace);
		super.releaseSession(session);
	}
	public void update(DepartmentFinace departmentFinace) {
		Session session=this.getSession();
		session.update(departmentFinace);
		super.releaseSession(session);
		
	}
	public DepartmentFinace  findDeptFinacebyDeptanddate (String dept, String date) {
		Session session=this.getSession();
		DepartmentFinace p=(DepartmentFinace) this.getSession().createQuery(" from DepartmentFinace where  date=?  and department=?").setParameter(0, date).setParameter(1,dept).uniqueResult();
		super.releaseSession(session);
		return p;
		
	}
	public double  countEmpcoffe (int dept ,int position) {
		Session session=this.getSession();
		Object p=this.getSession().createQuery(" select sum(postManager) from Employees where department.id='"+dept+"' and position="+position+"").uniqueResult();
		super.releaseSession(session);
		double temp = Double.parseDouble( p.toString());
		return temp;
		
	}
	
	public double  countDepartcoffe (int dept) {
		Session session=this.getSession();
		Object p=this.getSession().createQuery(" select sum(finaceCoffe) from Employees where department.id='"+dept+"'").uniqueResult();
		super.releaseSession(session);
		double temp = Double.parseDouble( p.toString());
		return temp;
		
	}
	//中层及普通
	public double  countFinaceCoffe (int position) {
		Session session   =this.getSession();
		Object p=this.getSession().createQuery(" select sum(finaceCoffe) from Employees where position=?").setParameter(0,position).uniqueResult();
		double temp = Double.parseDouble( p.toString());
		super.releaseSession(session);
		return temp;
		
	}
	
	public double  deptfinace (String dept, String date) {
		Session session   =this.getSession();
		Object p=this.getSession().createQuery(" select deptFinace  from DepartmentFinace where department=? and date=? ").setParameter(0,dept).setParameter(1, date).uniqueResult();
		double temp = Double.parseDouble( p.toString());
		super.releaseSession(session);
		return temp;
		
	}
	
	public List<FinacePercent> findFinacePercent() {
		Session session   =this.getSession();
		List<FinacePercent> p=this.getSession().createQuery("from FinacePercent").list();
		super.releaseSession(session);
		return p;
		
	}
	public void savaFinacePercent(FinacePercent f) {
	
		this.getHibernateTemplate().save(f);
		
	}
	public void updateFinacePercent(FinacePercent f) {
		
		this.getHibernateTemplate().update(f);
			
	}
	public void deleteFinacePercent(int id) {
		
		this.getHibernateTemplate().delete(this.getHibernateTemplate().load(FinacePercent.class, id));
	}
	public List<Finace> findFinace(String date) {
		Session session   =this.getSession();
		List<Finace> p=this.getSession().createQuery("from Finace where date=?").setParameter(0, date).list();
		super.releaseSession(session);
		return p;
		
	}
	public Finace findFinaceByNnameAndDate(String name,String date) {
		Session session   =this.getSession();
		Finace p=(Finace) this.getSession().createQuery("from Finace where name=? and date=?").setParameter(0, name).setParameter(1, date).uniqueResult();
		super.releaseSession(session);
		return p;
		
	}
	public void savaFinace(Finace f) {
		
		this.getHibernateTemplate().save(f);
		
	}
	public void updateFinace(Finace f) {
		
		this.getHibernateTemplate().update(f);
		
	}

	public LeaderReward findLeaderRewardByNnameAndDate(String name,String date) {
		Session session   =this.getSession();
		LeaderReward p=(LeaderReward) this.getSession().createQuery("from LeaderReward where name=? and date=?").setParameter(0, name).setParameter(1, date).uniqueResult();
		super.releaseSession(session);
		return p;
		
	}
	public List<LeaderReward> findLeaderFinace(String date) {
		Session session   =this.getSession();
		List<LeaderReward> p=this.getSession().createQuery("from LeaderReward where date=?").setParameter(0, date).list();
		super.releaseSession(session);
		return p;
		
	}
	public List<MiddleReward> findMiddleReward(String date) {
		Session session   =this.getSession();
		List<MiddleReward> p=this.getSession().createQuery("from MiddleReward where date=?").setParameter(0, date).list();
		super.releaseSession(session);
		return p;
		
	}
	public MiddleReward findMiddleRewardByNnameAndDate(String name,String date) {
		Session session   =this.getSession();
		MiddleReward p=(MiddleReward) this.getSession().createQuery("from MiddleReward where name=? and date=?").setParameter(0, name).setParameter(1, date).uniqueResult();
		super.releaseSession(session);
		return p;
		
	}
	public void savaLeaderReward(LeaderReward l) {
		
		this.getHibernateTemplate().save(l);
		
	}
	public void updateLeaderReward(LeaderReward l) {
		
		this.getHibernateTemplate().update(l);
		
	}
	public void savaMiddleReward(MiddleReward l) {
		
		this.getHibernateTemplate().save(l);
		
	}
	public void updateMiddleReward(MiddleReward l) {
		
		this.getHibernateTemplate().update(l);
		
	}
	public void updateDepartmentCoffer(DepartmentCoffer l) {
		
		this.getHibernateTemplate().update(l);
		
	}
	
	public List< DepartmentCoffer> findDeptCoffer(DetachedCriteria criteria, int firstResult,int maxResults) {
		return this.getHibernateTemplate().findByCriteria(criteria,firstResult,maxResults);
	}
	//查找总页数
	public int findByDeptCoffeTotalCount() {
		List list= this.getHibernateTemplate().find("select count(*) from DepartmentCoffer ");
		long temp = (Long) list.get(0);
		return (int) temp;
	}
	public List<AllRewardPercent> ListinFinacePercent() {
		Session session   =this.getSession();
		List<AllRewardPercent> p=this.getSession().createQuery("from AllRewardPercent").list();
		super.releaseSession(session);
		return p;
	}
	public void updateinFinacePercent(AllRewardPercent a) {	
		this.getHibernateTemplate().update(a);
		
	}
	public AllRewardPercent FindinFinacePercent() {
		Session session   =this.getSession();
		AllRewardPercent p=(AllRewardPercent) this.getSession().createQuery("from AllRewardPercent where id=1 ").uniqueResult();
		super.releaseSession(session);
		return p;
	}
	public List<RewardStock> listRewardStock() {
		
		Session session   =this.getSession();
		List<RewardStock> p= this.getSession().createQuery("from RewardStock ").list();
		super.releaseSession(session);
		return p;
	}
	public void upRewardStock(RewardStock r) {
		
		this.getHibernateTemplate().update(r);
	}
	public void saveRewardStock(RewardStock r) {
		
		this.getHibernateTemplate().save(r);
	}
	
	public RewardStock findRewardStock(String year) {
		
		Session session   =this.getSession();
		RewardStock  p= (RewardStock) this.getSession().createQuery("from RewardStock where year=? ").setParameter(0, year).uniqueResult();
		super.releaseSession(session);
		return p;
	}
	public double FindfinaceBydateAndName(String date,String name) {
		Session session   =this.getSession();
		Object p=this.getSession().createQuery(" select finace  from Finace where  date=? and name=? ").setParameter(0,date).setParameter(1, name).uniqueResult();
		double temp = Double.parseDouble( p.toString());
		super.releaseSession(session);
		return temp;
	}
	public List< DepartJobfinace> ListListDepartJobfinace(DetachedCriteria criteria, int firstResult,int maxResults) {
		return this.getHibernateTemplate().findByCriteria(criteria,firstResult,maxResults);
	}
	public void saveDepartJobfinace(DepartJobfinace l) {
			
			this.getHibernateTemplate().save(l);
			
		}
	public void updateDepartJobfinace(DepartJobfinace l) {
		
		this.getHibernateTemplate().update(l);
		
	}
	public DepartJobfinace findDepartJobfinacebyDate(String date) {
		Session session   =this.getSession();
		DepartJobfinace p=(DepartJobfinace) this.getSession().createQuery(" from DepartJobfinace where date=? ").setParameter(0,date).uniqueResult();
		super.releaseSession(session);
		return p;
	}
	public DepartmentCoffer FindDepartmentCofferByName(String name) {
		Session session   =this.getSession();
		DepartmentCoffer p=(DepartmentCoffer) this.getSession().createQuery(" from DepartmentCoffer where department=? ").setParameter(0,name).uniqueResult();
		super.releaseSession(session);
		return p;
		
	}
	/**
	 * 根据奖金存量表
	 * 计算所有科室的系数之和
	 * @return
	 */
	public double  countDepartmentcoffe () {
		Session session=this.getSession();
		Object p=this.getSession().createQuery(" select sum(coffe) from DepartmentCoffer").uniqueResult();
		super.releaseSession(session);
		double temp = Double.parseDouble( p.toString());
		return temp;
	}
	public List<DepartmentFinace> findDepartmentFinaceByDate(String date) {
		
		Session session   =this.getSession();
		List<DepartmentFinace> p= this.getSession().createQuery(" from DepartmentFinace where date=? ").setParameter(0,date).list();
		super.releaseSession(session);
		return p;
	}
	public double finddeptcorebydateanddept(String searchdate, Department d) {
		double a=0.0;
		double b=0.0;
		String sql1="SELECT SUM(x.m*z.coff ) as empcore  from (SELECT DISTINCT(r.empID) empid,r.actualworkday/r.workday m FROM `t_rate` r  where r.date=? and r.deID=?) x ,(SELECT id from t_employees e  where e.postManager=0 and e.deId=?) y ,(SELECT d.coffe as coff from t_departmentcoffer d where d.department=?) z WHERE x.empid=y.id";
		String sql2="SELECT SUM(w.m*t.man) as mancore from (SELECT DISTINCT(r.empID) empid,r.actualworkday/r.workday m FROM `t_rate` r  where r.date=? and r.deID=?) w ,(SELECT e.id as id,e.postManager as man from t_employees e  where e.postManager>0 and e.deId=? ) t where w.empid=t.id";
		//String sqlString="SELECT p.empcore+q.mancore from  (SELECT SUM(x.m*z.coff ) as empcore  from (SELECT DISTINCT(r.empID) empid,r.actualworkday/r.workday m FROM `t_rate` r  where r.date=? and r.deID=?) x ,(SELECT id from t_employees e  where e.postManager>0 and e.deId=?) y ,(SELECT d.coffe as coff from t_departmentcoffer d where d.department=?) z WHERE x.empid!=y.id) p ,(SELECT SUM(w.m*t.man) as mancore from (SELECT DISTINCT(r.empID) empid,r.actualworkday/r.workday m FROM `t_rate` r  where r.date=? and r.deID=?) w ,(SELECT e.id as id,e.postManager as man from t_employees e  where e.postManager>0 and e.deId=? ) t where w.empid=t.id) q";
		Object result=this.getSession().createSQLQuery(sql1).setParameter(0, searchdate).setParameter(1, d.getId()).setParameter(2, d.getId()).setParameter(3, d.getName()).uniqueResult();
		Object result1=this.getSession().createSQLQuery(sql2).setParameter(0, searchdate).setParameter(1, d.getId()).setParameter(2, d.getId()).uniqueResult();
		if (result!=null) {
			a=Double.parseDouble(result.toString());
		}
		if (result1!=null) {
			b=Double.parseDouble(result1.toString());
		}
		return a+b;
	}
	 
}
